﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
using thousandclear.Data;
using thousandClear.Areas.Admin.Models;

namespace thousandClear.Areas.Admin.Controllers
{
    public class ExportController : Controller
    {
        // GET: Admin/Export
        public ActionResult Index()
        {
            return View();
        }
        // GET: Admin/Export/OrderList
        public FileResult OrderList()
        {
            var sbHtml = new StringBuilder();
            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
            sbHtml.Append("<tr>");
            var lstTitle = new List<string> { "下单时间", "订单编号", "订单状态", "服务类型", "客户编号", "卖家昵称", "单价", "数量", "总金额", "开卡类型", "开卡时长", "赠送时长", "日常总次数", "日常剩余次数", "专项总次数", "专项剩余次数", "服务管家", "客户姓名", "联系电话", "房屋面积", "服务地址", "合同起始日期", "合同截止日期", "服务时间段", "专项项目"};
            foreach (var item in lstTitle)
            {
                sbHtml.AppendFormat(
                    "<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>",
                    item);
            }
            sbHtml.Append("</tr>");

            string[] sats = new[] { "", "已付款", "已冻结", "", "已分配", "已完成" };

            using (var db = new qianjingEntities())
            {
                var order = db.v_Order.OrderByDescending(a => a.CreatedDate).Where(a => a.RowStatus != 0).ToList();


                if (order != null)
                {
                    foreach (var i in order)
                    {
                        int ogs = 0;
                        if (i.toItemsGift != null)
                        {
                            if (i.toItemsGift.Trim() != "")
                            {
                                ogs = i.toItemsGift.Split(',').Length;
                            }
                        }

                        string SignDetail = "";
                        if (!string.IsNullOrEmpty(i.toItemsGift))
                        {
                            if (i.toItemsGift.Trim() != "")
                            {
                                string[] SauntID = i.toItemsGift.Split(',');
                                foreach (var ii in SauntID)
                                {
                                    SignDetail += db.D_item.FirstOrDefault(a => a.DIMID.ToString() == ii).dimDesc + "-";
                                }
                            }
                        }
                        sbHtml.Append("<tr>");
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.CreatedDate.ToString("yyyy-MM-dd hh:mm:ss"));
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.toSerialNo);
                        string sstt = "";
                        if (i.RowStatus == 1)
                        {
                            sstt = "已付款";
                        }
                        if (i.RowStatus == 2)
                        {
                            sstt = "已取消";

                        }
                        if (i.RowStatus == 3)
                        {
                            sstt = "已冻结";

                        }
                        if (i.RowStatus == 4)
                        {
                            sstt = "未付款";

                        }
                        if (i.RowStatus == 5)
                        {
                            sstt = "已分配";

                        }
                        if (i.RowStatus == 6)
                        {
                            sstt = "已完成";

                        }

                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", sstt);

                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(i.ogsDesc) ? "" : i.ogsDesc);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(i.oasNo) ? "" : i.oasNo);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(i.ocNicName) ? "" : i.ocNicName);

                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.toTotalRate);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.toGoodNum);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.toTotalRate * i.toGoodNum);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", "一月" + i.ogsTimeMonth + "次");
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.ogsSignTime + "个月");
                        string so = "";
                        if (i.ogsTimeMonth > 1)
                        {
                            so = ((i.ogsTime - i.ogsTimeMonth * i.ogsSignTime) / i.ogsTimeMonth).ToString() + "个月";
                        }

                        if (i.ogsTimeMonth <= 1)
                        {
                            so = "0个月";
                        }

                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", so);

                         //< td >@(item.ogsTime + item.yue) </ td >
  
                         //         < td >@(item.toTime) </ td >
    
                         //           < td >@(item.ogsSeqNum + item.toZengSongLieji) </ td >
      
                         //             < td > @item.toSeqNum </ td >

                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.ogsTime + int.Parse(i.UpdatedBy == null ? "0" : i.UpdatedBy.ToString()));
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.toTime);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.ogsSeqNum + (i.toZengSongLieji == null ? 0 : (int)i.toZengSongLieji));
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.CreatedBy == null ? "0" : i.CreatedBy);

                        string auntName = "";
                        if (!string.IsNullOrEmpty(i.toOATID))
                        {
                            string[] SauntID = i.toOATID.Split(',');
                            foreach (var iat in SauntID)
                            {
                                auntName += db.O_Aunt.FirstOrDefault(a => a.OATID.ToString() == iat).oatName + "-";
                            }

                        }
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", auntName);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.oasName);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.osaPhone);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.ogsArea);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.datDesc + i.datDesc1 + i.datDesc2 + i.oasAddressDetail);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.osaStartDay);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.osaEndDay);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i.osaTimeslot);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(SignDetail) ? "暂无" : SignDetail);
                        sbHtml.Append("</tr>");
                    }
                }

                //for (int i = 0; i < 1000; i++)
                //{
                //    sbHtml.Append("<tr>");
                //    sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i);
                //    sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>屌丝{0}号</td>", i);
                //    sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", new Random().Next(20, 30) + i);
                //    sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", DateTime.Now);
                //    sbHtml.Append("</tr>");
                //}
                sbHtml.Append("</table>");
            }

            //第一种:使用FileContentResult
            byte[] fileContents = Encoding.UTF8.GetBytes(sbHtml.ToString());
            return File(fileContents, "application/ms-excel", string.Format("{0}.xls", Guid.NewGuid().ToString()));
        }

        // GET: Admin/Export/RewardList
        public FileResult RewardList()
        {
            var sbHtml = new StringBuilder();
            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
            sbHtml.Append("<tr>");
            var lstTitle = new List<string> { "客户编号", "客户姓名", "管家姓名", "打赏积分", "打赏日期" };
            foreach (var item in lstTitle)
            {
                sbHtml.AppendFormat(
                    "<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>",
                    item);
            }
            sbHtml.Append("</tr>");

            //string[] sats = new[] { "", "已付款", "已冻结", "", "已分配", "已完成" };

            using (var db = new qianjingEntities())
            {
                var records = db.T_Reward.OrderByDescending(a => a.CreatedDate).ToList();


                if (records != null)
                {
                    foreach (var item in records)
                    {

                        string strNo = "";
                        string strCustName = "";
                        string strAuntName = "";

                        var NoList = db.O_Address.Where(a => a.oasOCID == item.trOCID).ToList();
                        foreach (var i in NoList)
                        {
                            strNo += i.oasNo;
                            strCustName = i.oasName;
                        }

                        List<string> LiOaid = item.trOAID.Split(',').ToList();
                        foreach (var i in LiOaid)
                        {
                            var NameList = db.O_Aunt.Where(a => a.OATID.ToString() == i).FirstOrDefault();

                            strAuntName += NameList.oatName;
                        }

                        sbHtml.Append("<tr>");
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(strNo)?"N/A": strNo);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(strCustName) ? "N/A" : strCustName);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(strAuntName) ? "N/A" : strAuntName);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", item.trTotolRate);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", item.CreatedDate.ToString("yyyy-MM-dd hh:mm:ss"));                        
                        sbHtml.Append("</tr>");
                    }
                }
                sbHtml.Append("</table>");
            }

            //第一种:使用FileContentResult
            byte[] fileContents = Encoding.UTF8.GetBytes(sbHtml.ToString());
            return File(fileContents, "application/ms-excel", string.Format("{0}.xls", Guid.NewGuid().ToString()));
        }

        
        // GET: Admin/Export/RewardList
        public FileResult ClearLog(string id)
        {
            var sbHtml = new StringBuilder();
            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
            sbHtml.Append("<tr>");
            var lstTitle = new List<string> { "打扫日期","客户编号", "客户姓名", "客户地址" };
            foreach (var item in lstTitle)
            {
                sbHtml.AppendFormat(
                    "<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>",
                    item);
            }
            sbHtml.Append("</tr>");

            //string[] sats = new[] { "", "已付款", "已冻结", "", "已分配", "已完成" };

            using (var db = new qianjingEntities())
            {
                var records = db.SP_clearLog_info(id).OrderByDescending(a => a.CreatedDate).ToList();


                if (records != null)
                {
                    foreach (var item in records)
                    {
                        sbHtml.Append("<tr>");
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", item.CreatedDate.ToString("yyyy-MM-dd hh:mm:ss"));
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(item.oasNo) ? "N/A" : item.oasNo);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(item.oasName) ? "N/A" : item.oasName);
                        sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", string.IsNullOrEmpty(item.oasAddressDetail) ? "N/A" : item.oasAddressDetail);
                        sbHtml.Append("</tr>");
                    }
                }
                sbHtml.Append("</table>");
            }

            //第一种:使用FileContentResult
            byte[] fileContents = Encoding.UTF8.GetBytes(sbHtml.ToString());
            return File(fileContents, "application/ms-excel", string.Format("{0}.xls", Guid.NewGuid().ToString()));
        }

        #region 批量导入基站
        public ActionResult StationImport()
        {
            return View();
        }



        [HttpPost]
        public ActionResult StationImport(HttpPostedFileBase filebase)
        {
            HttpPostedFileBase file = Request.Files["files"];
            string FileName;
            string savePath;
            if (file == null || file.ContentLength <= 0)
            {
                ViewBag.error = "文件不能为空";
                return View();
            }
            else
            {
                string filename = Path.GetFileName(file.FileName);
                int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
                string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
                string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
                int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
                string FileType = ".xls,.xlsx";//定义上传文件的类型字符串

                FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
                if (!FileType.Contains(fileEx))
                {
                    ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
                    return View();
                }
                if (filesize >= Maxsize)
                {
                    ViewBag.error = "上传文件超过4M，不能上传";
                    return View();
                }
                string path = AppDomain.CurrentDomain.BaseDirectory + "uploads/excel/";
                savePath = Path.Combine(path, FileName);
                file.SaveAs(savePath);
            }

            //string result = string.Empty;
            string strConn;
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + savePath + ";Extended Properties='Excel 12.0;IMEX=1;HDR=No;'";
            //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [现有客户 $]", strConn);
            DataSet myDataSet = new DataSet();
            try
            {
                myCommand.Fill(myDataSet, "ExcelInfo");
            }
            catch (Exception ex)
            {
                ViewBag.error = ex.Message;
                return View();
            }
            DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
            int f;
            int n;
            int goodid;
            try
            {
                //引用事务机制，出错时，事物回滚
                using (var db = new qianjingEntities())
                {
                    for (int i = 1; i < table.Rows.Count; i++)
                    {
                        //获取地区名称
                        string _oasNo = table.Rows[i][0].ToString();
                        string _oasName = table.Rows[i][1].ToString();
                        string _osaPhone = table.Rows[i][2].ToString();
                        string _datDesc = table.Rows[i][3].ToString();
                        string _oasAddressDetail = table.Rows[i][4].ToString();
                        string _ogsArea = table.Rows[i][5].ToString();
                        string _ogsTimeMonth = table.Rows[i][6].ToString();
                        string _toSignTime = table.Rows[i][7].ToString();
                        string _osaStartDay = table.Rows[i][8].ToString();
                        string _osaEndDay = table.Rows[i][9].ToString();
                        string _toTime = table.Rows[i][11].ToString();
                        string _CreatedBy = table.Rows[i][13].ToString();
                        string _ogsSignTime = table.Rows[i][14].ToString();
                        var addressID = db.D_Angent.FirstOrDefault(a => a.datDesc == _datDesc && a.RowStatus == 1 && a.datLevel == 3).DATID;
                        string Number = DateTime.Now.ToString("yyMMddHHmmss");
                        Random example = new Random();
                        int Numradom = example.Next(10000, 99999);
                        Number = Number + Numradom.ToString();
                        db.O_Address.Add(new O_Address
                        {
                            oasName = _oasName,
                            oasNo = _oasNo,
                            osaPhone = _osaPhone,
                            oasDATID =Convert.ToInt16(addressID),
                            osaStartDay = _osaStartDay,
                            osaEndDay = _osaEndDay,
                            RowStatus = 1,
                            CreatedDate = DateTime.Now,
                            oasAddressDetail=_oasAddressDetail
                        });
                        db.SaveChanges();
                        var OAID = db.O_Address.FirstOrDefault(a => a.oasNo == _oasNo).OASID;
                        f = Convert.ToInt16(_ogsTimeMonth);
                        n = Convert.ToInt16(_ogsSignTime);
                        var good = db.O_Goods.FirstOrDefault(a => a.ogsTimeMonth ==f  && a.ogsArea == _ogsArea && a.ogsSignTime == n && a.RowStatus == 1);
                        if (good == null)
                        {
                            goodid = 99;
                        }
                        else {
                            goodid =Convert.ToInt16(good.OGSID);
                        }
                        db.T_Order.Add(new T_Order
                        {
                            toOGID = Convert.ToInt16(goodid),
                            toOAID = Convert.ToInt16(OAID),
                            toTotalRate = 0,
                            toSerialNo = Number,
                            toTime = Convert.ToInt16(_toTime),
                            CreatedBy = _CreatedBy,
                            toSignTime = Convert.ToInt16(_toSignTime),
                            CreatedDate = DateTime.Now,
                            RowStatus = 1,

                        });
                        db.SaveChanges();


                    }
                }
                ViewBag.error = "导入成功";
                System.Threading.Thread.Sleep(2000);
                return RedirectToAction("Index");

            }
            catch (Exception ex)
            {
                ViewBag.error = ex.Message;
                return View();
            }
            #endregion
        }
    }
}
